/*
 *Copyright(c) 2021 Shenzhen Kaihong Digital Industry DevelopmentCo.,Ltd
 *Licensed under the Apache License,Version2.0(the"License");
 *you may not use this file except in compliance with the License.
 *You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,software
 * distributed under the License is distributed on an "AS IS"BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.chinasoft.service.impl;

import com.chinasoft.bean.po.*;
import com.chinasoft.bean.vo.AddSampleVo;
import com.chinasoft.mapper.SampleMapper;
import com.chinasoft.service.*;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * Created by Guanzhong Hu
 * Date :2020/2/12
 * Description : excel导入工具类
 * Version :1.0
 */
@Service
public class ExcelHandler {

    private final static String excel2003L = ".xls";    //2003- 版本的excel
    private final static String excel2007U = ".xlsx";   //2007+ 版本的excel
    private final static String SIGN_YES = "是";
    private final static String DEVICE_DEV = "设备开发";
    private final static String APP_DEV = "应用开发";

    @Autowired
    private TitleService titleService;

    @Autowired
    private OsTypeService osTypeService;

    @Autowired
    private OsVersionService osVersionService;

    @Autowired
    private BusTypeService busTypeService;

    @Autowired
    private FeaturesService featuresService;

    @Autowired
    private SampleService sampleService;

    @Autowired
    private BaseInfoService baseInfoService;

    @Autowired
    private DetailTypeService detailTypeService;

    @Autowired
    private DetailService detailService;

    /**
     * @param in,fileName
     * @return
     * @throws IOException
     * @Description：获取IO流中的数据，组装成List<List<Object>>对象
     */
    public void getListByExcel(InputStream in, String fileName) throws Exception {

        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;  //页数
        Row row = null;  //行数
        Cell cell = null;  //列数
        Title title = null; //标题对象

        List<Title> titleList = titleService.findAll(new Title());
        List<OsType> osTypeList = osTypeService.findAll(new OsType());
        List<OsVersion> osVersionList = osVersionService.findAll(new OsVersion());
        List<Features> featuresList = featuresService.findAll(new Features());


        List<DetailType> detailTypeList = detailTypeService.findAll(new DetailType());
        Map<Integer, String> detaialTypeMap = Maps.newHashMap();
        detailTypeList.forEach(vo -> detaialTypeMap.put(vo.getId(), vo.getName()));
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }

            String sheetName = sheet.getSheetName();
            if (StringUtils.isEmpty(sheetName)) {
                continue;
            }
            //获取标题
            long count = titleList.stream().filter(vo -> vo.getName().contains(sheetName)).count();
            if (count <= 0) {
                continue;
            }
            title = titleList.stream().filter(vo -> vo.getName().contains(sheetName)).findFirst().get();

            DataVO dataVO = new DataVO();

            //遍历当前sheet中的所有行
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                if (0 == j) {
                    dataVO = dataList(row, sheet.getRow(j + 1));
                    continue;
                }
                if (1 == j) {
                    continue;
                }
                List<Object> li = new ArrayList<Object>();

                AddSampleVo sample = new AddSampleVo();
                //关联标题
                sample.setTitleId(title.getId().toString());

                List<BaseInfo> baseInfoList = new ArrayList<>();
                baseInfoList.add(new BaseInfo());
                OsVersion osVersion = new OsVersion();
                OsType osType = new OsType();

                List<Detail> detailList = new ArrayList<>();

                boolean flagNull = false;

                //遍历所有的列
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    String value = getValue(cell);
                    String address = ""; //gitlab地址
                    if (0 == y) {
                        //样例名称为空，则是错误数据
                        if (StringUtils.isEmpty(value)) {
                            flagNull = true;
                            break;
                        }
                        Hyperlink hyperlink = cell.getHyperlink();
                        if (null != hyperlink) {
                            address = hyperlink.getAddress();
                        }
                    }
                    if (sheetName.contains(DEVICE_DEV)) {
                        switch (y) {
                            case 0:
                                sample.setName(value);
                                sample.setGitUrl(address);
                                break;
                            case 1:
                                baseInfoList.get(0).setSysVersion(value);
                                long versionCount = osVersionList.stream().filter(vo -> vo.getName().contains(value)).count();
                                if (versionCount > 0) {
                                    osVersion = osVersionList.stream().filter(vo -> vo.getName().contains(value)).findFirst().get();
                                    sample.setOsVersionId(osVersion.getId().toString());
                                }
                                break;
                            case 2:
                                sample.setSummary(value);
                                break;
                            case 3:
                                if (StringUtils.isEmpty(value)) {
                                    sample.setCreateTime(new Date());
                                    break;
                                }
                                if (value.length() <= 4) {
                                    int year = Calendar.getInstance().get(Calendar.YEAR);
                                    String newValue = year + "-" + value.substring(0, 2) + "-" + value.substring(2);
                                    sample.setCreateTime(sdf.parse(newValue));
                                    break;
                                }
                                sample.setCreateTime(sdf.parse(value));
                                break;
                            case 4:
                                if (!StringUtils.isEmpty(value)) {
                                    sample.setImageUrl(value);
                                }
                                break;
                            case 5:
                                sample.setSign(0);
                                if (!StringUtils.isEmpty(value) && SIGN_YES.equals(value)) {
                                    sample.setSign(1);
                                }
                                break;
                            case 6:
                                if (!StringUtils.isEmpty(value)) {
                                    sample.setDifficultyLevel(value);
                                }
                                break;
                            case 7:
                                baseInfoList.get(0).setDeviceLevel(value);
                                if (!StringUtils.isEmpty(value)) {
                                    long typeCount = osTypeList.stream().filter(vo -> vo.getName().contains(value)).count();
                                    if (typeCount > 0) {
                                        osType = osTypeList.stream().filter(vo -> vo.getName().contains(value)).findFirst().get();
                                        sample.setOsTypeId(osType.getId().toString());
                                    }
                                }
                                break;
                            case 8:
                                sample.setAuthor(value);
                                break;
                            case 9:
                                break;
                            case 10:
                                versionNoHandler(baseInfoList, value);
                                break;
                            case 11:
                                detailHandler(detailTypeList, detailList, value);
                                break;
                            default:
                                busTypeHandler(sample, dataVO, title.getId(), y, value);
                                break;
                        }
                    } else if (sheetName.contains(APP_DEV)) {
                        switch (y) {
                            case 0:
                                sample.setName(value);
                                sample.setGitUrl(address);
                                break;
                            case 1:
                                sample.setSummary(value);
                                break;
                            case 2:
                                if (StringUtils.isEmpty(value)) {
                                    sample.setCreateTime(new Date());
                                    break;
                                }
                                if (!StringUtils.isEmpty(value) && value.length() <= 4) {
                                    int year = Calendar.getInstance().get(Calendar.YEAR);
                                    String newValue = year + "-" + value.substring(0, 2) + "-" + value.substring(2);
                                    sample.setCreateTime(sdf.parse(newValue));
                                    break;
                                }
                                sample.setCreateTime(sdf.parse(value));
                                break;
                            case 3:
                                if (!StringUtils.isEmpty(value)) {
                                    sample.setImageUrl(value);
                                }
                                break;
                            case 4:
                                sample.setSign(0);
                                if (!StringUtils.isEmpty(value) && SIGN_YES.equals(value)) {
                                    sample.setSign(1);
                                }
                                break;
                            case 5:
                                if (!StringUtils.isEmpty(value)) {
                                    sample.setDifficultyLevel(value);
                                }
                                break;
                            case 6:
                                sample.setAuthor(value);
                                break;
                            case 7:
                                break;
                            case 8:
                                baseInfoList.get(0).setSysVersion(value);
                                if (!StringUtils.isEmpty(value)) {
                                    String newValue = value.replace("\r", "").trim();
                                    long versionCount = osVersionList.stream().filter(vo -> vo.getName().contains(newValue)).count();
                                    if (versionCount > 0) {
                                        osVersion = osVersionList.stream().filter(vo -> vo.getName().contains(newValue)).findFirst().get();
                                        sample.setOsVersionId(osVersion.getId().toString());
                                    }
                                }
                                break;
                            case 9:
                                baseInfoList.get(0).setDeviceLevel(value);
                                long typeCount = osTypeList.stream().filter(vo -> vo.getName().contains(value)).count();
                                if (typeCount > 0) {
                                    osType = osTypeList.stream().filter(vo -> vo.getName().contains(value)).findFirst().get();
                                    sample.setOsTypeId(osType.getId().toString());
                                }
                                break;
                            case 10:
                                versionNoHandler(baseInfoList, value);
                                break;
                            case 11:
                                detailHandler(detailTypeList, detailList, value);
                                break;
                            default:
                                // 业务类型，特性
                                busTypeHandler(sample, dataVO, title.getId(), y, value);
                                break;
                        }
                    }
                }
                if (flagNull) {
                    continue;
                }

                //样例信息
                sampleService.addSample(sample);

                // 保存基本信息
                if (!baseInfoList.isEmpty()) {
                    baseInfoList.forEach(vo -> {
                        vo.setSampleId(sample.getId());
                        baseInfoService.add(vo);
                    });
                }

                // 描述信息
                detailList.forEach(vo -> {
                    vo.setSampleId(sample.getId());
                    Detail detail = detailService.find(vo);
                    if (null == detail || detail.getId() <= 0) {
                        detailService.add(vo);
                    }
                });
            }
        }
    }

    /**
     * 处理版本
     *
     * @param baseInfoList
     * @param value
     */
    private void versionNoHandler(List<BaseInfo> baseInfoList, String value) {
        if (value.contains("\n")) {
            List<String> valueList = Lists.newArrayList(value.split("\n"));
            valueList = valueList.stream().filter(str -> !StringUtils.isEmpty(str)).collect(Collectors.toList());
            if (valueList.size() > 1) {
                BaseInfo baseInfo = baseInfoList.get(0);
                List<String> versionList = Lists.newArrayList();
                if (!StringUtils.isEmpty(baseInfo.getSysVersion()) && baseInfo.getSysVersion().contains("\n")) {
                    versionList = Lists.newArrayList(baseInfo.getSysVersion().split("\n")).stream().filter(str -> !StringUtils.isEmpty(str)).collect(Collectors.toList());
                } else {
                    versionList.add(baseInfo.getSysVersion());
                }
                List<BaseInfo> newBaseList = Lists.newArrayList();
                for (String item : valueList) {
                    if (StringUtils.isEmpty(item)) {
                        continue;
                    }
                    int indexOf = valueList.indexOf(item);
                    BaseInfo info = new BaseInfo();
                    info.setDeviceLevel(baseInfo.getDeviceLevel());
                    info.setSysVersion(versionList.get(indexOf >= versionList.size() ? versionList.size() - 1 : indexOf));
                    info.setVersionNo(item);
                    newBaseList.add(info);
                }
                baseInfoList.clear();
                baseInfoList.addAll(newBaseList);
            } else {
                baseInfoList.get(0).setVersionNo(value);
            }
        } else {
            baseInfoList.get(0).setVersionNo(value);
        }
    }

    /**
     * 处理业务类型
     *
     * @param sample
     * @param dataVO
     * @param titleId
     * @param y
     * @param value
     */
    private void busTypeHandler(AddSampleVo sample, DataVO dataVO, int titleId, int y, String value) {
        if (StringUtils.isEmpty(value)) {
            return;
        }
        String bvalue = dataVO.getBrow().get(y);
        BusType busType = new BusType();
        busType.setName(bvalue);
        busType.setTitleId(titleId);
//        busType.setLevelId(levelId);
        BusType find = busTypeService.find(busType);
        if (null != find && find.getId() > 0) {
            if (!StringUtils.isEmpty(sample.getBusTypeId())) {
                Set<String> busTypeSet = Sets.newHashSet(sample.getBusTypeId().split(","));
                busTypeSet.add(find.getId().toString());
                sample.setBusTypeId(busTypeSet.stream().collect(Collectors.joining(",")));
            } else {
                sample.setBusTypeId(find.getId().toString());
            }
            busType.setId(find.getId());
        }

//        if (null == find || find.getId() <= 0){
//            busTypeService.add(busType);
//        } else {
//            busType.setName(find.getName());
//            busType.setId(find.getId());
//        }

        String fvalue = dataVO.getFrow().get(y);
        Features features = new Features();
        features.setName(fvalue);
        features.setBusTypeId(busType.getId());
        Features findF = featuresService.find(features);
        if (null != findF && findF.getId() > 0) {
            if (!StringUtils.isEmpty(sample.getFeaturesIds())) {
                Set<String> feaSet = Sets.newHashSet(sample.getFeaturesIds().split(","));
                feaSet.add(findF.getId().toString());
                sample.setFeaturesIds(feaSet.stream().collect(Collectors.joining(",")));
            } else {
                sample.setFeaturesIds(findF.getId().toString());
            }
        }
    }

    /**
     * 处理学习资料的问题
     *
     * @param detailTypeList
     * @param detailList
     * @param value
     */
    private void detailHandler(List<DetailType> detailTypeList, List<Detail> detailList, String value) {
        List<String> resultList = Lists.newArrayList(value.split("\n"));
        List<Detail> handlerList = Lists.newArrayList();
        for (String val : resultList) {
            if (StringUtils.isEmpty(val) || (!StringUtils.isEmpty(val) && StringUtils.isEmpty(val.trim())) || (!StringUtils.isEmpty(val) && val.equals("\r"))) {
                continue;
            }
            String valstr = val.replace("\r", "");

            long count = detailTypeList.stream().filter(vo -> {
                if (valstr.contains(vo.getName()) && valstr.length() <= 6) {
                    return true;
                }
                if (valstr.length() >= 4 && valstr.length() <= 6 && (vo.getName().contains(valstr.substring(0, 2)) ||
                        vo.getName().contains(valstr.substring(2, valstr.length()).replace(":", "")))) {
                    return true;
                }
                return false;
            }).count();
            if (count > 0) {
                DetailType detailType = detailTypeList.stream().filter(vo -> {
                    if (valstr.contains(vo.getName()) && valstr.length() <= 6) {
                        return true;
                    }
                    if (valstr.length() >= 4 && valstr.length() <= 6 && (vo.getName().contains(valstr.substring(0, 2)) || vo.getName().contains(valstr.substring(2, valstr.length()).replace(":", "")))) {
                        return true;
                    }
                    return false;
                }).findFirst().get();
                Detail detail = new Detail();
                detail.setTypeId(detailType.getId());
                handlerList.add(detail);
            } else {

                if (handlerList.isEmpty()) {
                    Detail detail = new Detail();
                    detail.setTypeId(detailTypeList.get(1).getId());
                    handlerList.add(detail);
                }
                Detail detail = handlerList.get(handlerList.size() - 1);
                if (StringUtils.isEmpty(detail.getDescription())) {
                    detail.setDescription(valstr);
                    continue;
                }
                if (!StringUtils.isEmpty(detail.getDescription()) && StringUtils.isEmpty(detail.getUrl())) {
                    detail.setUrl(valstr);
                    continue;
                }
                Detail detailVO = new Detail();
                detailVO.setDescription(valstr);
                detailVO.setTypeId(detail.getTypeId());
                handlerList.add(detailVO);
            }
        }
        detailList.addAll(handlerList);
    }

    static class DataVO {
        Map<Integer, String> brow = new HashMap<>();
        Map<Integer, String> frow = new HashMap<>();
        int index;

        public Map<Integer, String> getBrow() {
            return brow;
        }

        public void setBrow(Map<Integer, String> brow) {
            this.brow = brow;
        }

        public Map<Integer, String> getFrow() {
            return frow;
        }

        public void setFrow(Map<Integer, String> frow) {
            this.frow = frow;
        }

        public int getIndex() {
            return index;
        }

        public void setIndex(int index) {
            this.index = index;
        }
    }

    /**
     * @return
     */
    private DataVO dataList(Row brow, Row frow) {
        DataVO dataVO = new DataVO();
        Map<Integer, String> bmap = new HashMap<>();
        Map<Integer, String> fmap = new HashMap<>();
        boolean flag = true;
        for (int i = brow.getFirstCellNum(); i < brow.getLastCellNum(); i++) {
            Cell cell = brow.getCell(i);
            String value = getValue(cell);
            if (!StringUtils.isEmpty(value)) {
                if (value.contains("学习材料")) {
                    flag = false;
                    continue;
                }
            }
            if (flag) {
                dataVO.setIndex(i + 1);
                continue;
            }

            Cell fcell = frow.getCell(i);
            String fvalue = getValue(fcell);
            if (StringUtils.isEmpty(value) && StringUtils.isEmpty(fvalue)) {
                continue;
            }
            value = StringUtils.isEmpty(value) ? bmap.get(i - 1) : value;
            bmap.put(i, value);
            fmap.put(i, fvalue);
            System.out.println("value=" + value + "---------" + getValue(fcell));
        }
        dataVO.setBrow(bmap);
        dataVO.setFrow(fmap);
        return dataVO;
    }

    /**
     * @param inStr,fileName
     * @return
     * @throws Exception
     * @Description：根据文件后缀，自适应上传文件的版本
     */
    public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            wb = new HSSFWorkbook(inStr);  //2003-
        } else if (excel2007U.equals(fileType)) {
            wb = new XSSFWorkbook(inStr);  //2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * @param cell
     * @return
     * @Description：对表格中数值进行格式化
     */
    //解决excel类型问题，获得数值
    public static String getValue(Cell cell) {
        String value = "";
        if (null == cell) {
            return value;
        }
        switch (cell.getCellType()) {
            //数值型
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //如果是date类型则 ，获取该cell的date值
                    Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                    value = format.format(date);
                    ;
                } else {// 纯数字
                    BigDecimal big = new BigDecimal(cell.getNumericCellValue());
                    value = big.toString();
                    //解决1234.0  去掉后面的.0
                    if (null != value && !"".equals(value.trim())) {
                        String[] item = value.split("[.]");
                        if (1 < item.length && "0".equals(item[1])) {
                            value = item[0];
                        }
                    }
                }
                break;
            //字符串类型
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue().toString();
                break;
            // 公式类型
            case Cell.CELL_TYPE_FORMULA:
                //读公式计算值
                value = String.valueOf(cell.getNumericCellValue());
                if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                    value = cell.getStringCellValue().toString();
                }
                break;
            // 布尔类型
            case Cell.CELL_TYPE_BOOLEAN:
                value = " " + cell.getBooleanCellValue();
                break;
            default:
                value = cell.getStringCellValue().toString();
        }
        if ("null".endsWith(value.trim())) {
            value = "";
        }
        return value;
    }

    public static void main(String[] args) throws Exception {
//        String filepath = "C:\\Users\\admin\\Desktop\\开发者成长之路2.0_openharmony_new.xlsx";
//        FileInputStream inputStream = new FileInputStream(new File(filepath));
//        new ExcelHandler().getListByExcel(inputStream, filepath);
        String str1 = "视频教程";
        String str2 = "官网资料";
        System.out.println(str1.substring(0, 2));
    }
}